<?php
/*----------------------------------------------------------------------
 * 统计管理
 * Author: ming.nie
----------------------------------------------------------------------*/

namespace Admin\Controller;

use Think\Controller;
class AnalyseController extends AdminController
{
    //财务统计
    public function cinemaFinancialAnalyse()
    {
        //查询条件参数
        $create_time_start = I('get.create_time_start');
        $create_time_end = I('get.create_time_end');
        
        $model = M();
        
        //附加查询条件
        $select_sql = '';
        $select_sql_2 = '';
        if($create_time_start) {
            $select_sql .= " AND (a.create_time >= ".strtotime($create_time_start).")";
            $select_sql_2 .= " AND (a.join_time >= ".strtotime($create_time_start).")";
        }
		if($create_time_end) {
            $create_time_end_data = strtotime($create_time_end);
            $create_time_end_data += 86400;
            $select_sql .= " AND (a.create_time <= ".$create_time_end_data.")";
            $select_sql_2 .= " AND (a.join_time <= ".$create_time_end_data.")";
        }
        
        
        //总用户数
        $sql = 'SELECT COUNT(id) AS count 
                FROM mt_cinema_host AS a';
        $rs = $model->query($sql);
        $cinema_count = $rs[0]['count'];
        
        //付费用户数
        $sql = 'SELECT cinema_host_id 
                FROM mt_recharge_log AS a
                WHERE recharge_type = 1 AND money > 0 
                GROUP BY cinema_host_id';
        $rs = $model->query($sql);
        $pay_cinema_count = count($rs);
        
        //充值次数
        $sql = 'SELECT COUNT(recharge_log_id) AS count  
                FROM mt_recharge_log AS a
                WHERE recharge_type = 1 AND money > 0'.$select_sql;
        $rs = $model->query($sql);
        $recharge_count = $rs[0]['count'];
        
        //充值金额
        $sql = 'SELECT SUM(money) AS recharge_money  
                FROM mt_recharge_log AS a
                WHERE recharge_type = 1 AND money > 0'.$select_sql;
        $rs = $model->query($sql);
        $recharge_money = $rs[0]['recharge_money'];
        
        //新增用户数
        $sql = 'SELECT COUNT(id) AS count 
                FROM mt_cinema_host AS a 
                WHERE 1'.$select_sql_2;
        $rs = $model->query($sql);
        $add_cinema_count = $rs[0]['count'];
		
		//列表
		/*查询条件*/ 
		if($_GET['create_time_start'] && $_GET['create_time_end']){
			$map['mt_recharge_log.create_time'] = array(array('EGT',strtotime($_GET['create_time_start'])  ),array('ELT',strtotime($_GET['create_time_end'])+86400 )) ;
		}elseif($_GET['create_time_start']){
			$map['mt_recharge_log.create_time'] = array('EGT',strtotime($_GET['create_time_start']));
		}elseif($_GET['create_time_end']){
			$map['mt_recharge_log.create_time'] = array('ELT',strtotime($_GET['create_time_end'])+86400);
		}
		$keywords = $_GET['keywords'];
		if($keywords){
			$map['mt_cinema_host.host_name'] = array('like',"%$keywords%");
		}
		if($keywords == '0'){
			$map['mt_cinema_host.host_name'] = array('like',"%$keywords%");
		}
		$map['mt_recharge_log.recharge_type'] = array('eq',2);;
		//统计总消费
		$countMoney  = M('recharge_log')
					->where($map)
					->join('mt_cinema_host ON mt_recharge_log.cinema_host_id = mt_cinema_host.id')
					->Sum("mt_recharge_log.money");
					// 查询满足要求的总记录数

		//分页
		$count  = M('recharge_log')
				->where($map)
				->join('mt_cinema_host ON mt_recharge_log.cinema_host_id = mt_cinema_host.id')	
				->count();// 查询满足要求的总记录数
		$Page   = new \Think\Page2($count,20);// 实例化分页类 传入总记录数和每页显示的记录数(10)
		$show   = $Page->show();// 分页显示输出
		$list   = M('recharge_log')
				->where($map)
				->field('mt_recharge_log.recharge_log_id ,mt_recharge_log.sn, mt_recharge_log.create_time, mt_recharge_log.money, mt_recharge_log.balance, mt_cinema_host.host_name ')
				->join('mt_cinema_host ON mt_recharge_log.cinema_host_id = mt_cinema_host.id')				
				->order('recharge_log_id desc')
				->limit($Page->firstRow.','.$Page->listRows)
				->select();	
        $page_title = '消费流水';
        $this->assign(get_defined_vars());
        $this->display('cinemaFinancialAnalyse');
    }
	
		//消费详情
	    public function cinemaFinancialDetail(){
			$map['mt_recharge_log.recharge_log_id'] = I('request.recharge_log_id');
			$info = M('recharge_log')
					->where($map)
					->field('mt_recharge_log.recharge_log_id ,mt_recharge_log.sn,mt_recharge_log.balance,mt_cinema_host.join_time, mt_cinema_host.last_cost_time, mt_cinema_host.deduct_period, mt_cinema_host.period_count , mt_recharge_log.create_time, mt_recharge_log.money, mt_recharge_log.status, mt_cinema_host.host_name, mt_cinema_host.cinema_name')
					->join('mt_cinema_host ON mt_recharge_log.cinema_host_id = mt_cinema_host.id')
					->find();
			$page_title = '消费流水详情';
			$this->assign(get_defined_vars());		
			$this->display('cinemaFinancialDetail');
		}
		
		//到款登记列表
		public function  cinemaFinancialReg(){
			/*查询条件*/ 
			if($_GET['create_time_start'] && $_GET['create_time_end']){
				$map['mt_recharge_log.create_time'] = array(array('EGT',strtotime($_GET['create_time_start'])  ),array('ELT',strtotime($_GET['create_time_end'])+86400 )) ;
			}elseif($_GET['create_time_start']){
				$map['mt_recharge_log.create_time'] = array('EGT',strtotime($_GET['create_time_start']));
			}elseif($_GET['create_time_end']){
				$map['mt_recharge_log.create_time'] = array('ELT',strtotime($_GET['create_time_end']));
			}
			if($_GET['status'] == 1){
				$map['mt_recharge_log.status'] = 1;
			}elseif($_GET['status'] == 2){
				$map['mt_recharge_log.status'] = 0;
			}
			$map['mt_recharge_log.recharge_type'] = 1; //类型(1：充值，2：消费)
			$keywords = $_GET['keywords'];
			if($keywords){
				$where['mt_cinema_host.host_name']  = array('like', "%$keywords%");
				$where['mt_cinema_host.cinema_name']  = array('like',"%$keywords%");
				$where['_logic'] = 'or';
				$map['_complex'] = $where;
			}
			if($keywords == '0'){
				$where['mt_cinema_host.host_name']  = array('like', "%$keywords%");
				$where['mt_cinema_host.cinema_name']  = array('like',"%$keywords%");
				$where['_logic'] = 'or';
				$map['_complex'] = $where;
			}
			$count  = M('recharge_log')
						->where($map)
						->join('mt_cinema_host ON mt_recharge_log.cinema_host_id = mt_cinema_host.id')
						->count();// 查询满足要求的总记录数
			$Page   = new \Think\Page2($count,20);// 实例化分页类 传入总记录数和每页显示的记录数(10)
			$show   = $Page->show();// 分页显示输出
			$list   = M('recharge_log')
						->where($map)
						->field('mt_recharge_log.recharge_log_id ,mt_recharge_log.sn, mt_recharge_log.create_time, mt_recharge_log.money, mt_recharge_log.status, mt_cinema_host.host_name, mt_cinema_host.cinema_name')
						->join('mt_cinema_host ON mt_recharge_log.cinema_host_id = mt_cinema_host.id')
						->order('recharge_log_id desc')
						->limit($Page->firstRow.','.$Page->listRows)
						->select();	
			$page_title = '到款列表';
			$this->assign(get_defined_vars());
			$this->display('cinemaFinancialReg');	
		} 

		//到款登记
		public function cinemaFinancialCheckin(){
			$map['recharge_log_id'] = I('request.recharge_log_id');
			$info = M('recharge_log')->where($map)->find();
			//获取公司充值银行
			$bank_account_options = getBankAccountOptions();
			//获取公司帐号
			$bank_num_options = getBankAccountMapOptions();
			$page_title = '到款登记';
			$this->assign(get_defined_vars());
			$this->display('cinemaFinancialCheckin');	
		}
		//到款登记详情
		public function cinemaFinancialCheckinDetail(){
			$map['recharge_log_id'] = I('request.recharge_log_id');
			$info = M('recharge_log')->where($map)->find();
			//获取公司充值银行
			$bank_account_options = getBankAccountOptions();
			//获取公司帐号
			$bank_num_options = getBankAccountMapOptions();			
			$page_title = '到款登记';
			$this->assign(get_defined_vars());
			$this->display('cinemaFinancialCheckinDetail');	
		}		
		
		//到款登记状态更新
		public function cinemaFinancialUpdate(){
			$map['recharge_log_id'] = I('request.recharge_log_id');
			//完成交易流程
			$cinemaLogic = new \Common\Logic\CinemaLogic();
			$complete_recharge_result = $cinemaLogic->completeRecharge($map);
			if(1 == $complete_recharge_result['status_code']) {
				$data['status'] = 1 ;
				$data['is_complete'] = 1 ;
				$data['update_time'] = time() ;
				$result = M('recharge_log')-> where($map)->setField($data);
				if($result != false){
					$msg['info'] = '到款登记成功';
					$msg['status'] = 1;
					$msg['url'] = U('Admin/Analyse/cinemaFinancialReg') ;
				}else{
					$msg['info'] = '到款登记失败';
					$msg['status'] = 2;
					$msg['url'] = U('Admin/Analyse/cinemaFinancialReg') ;
				}
			} elseif (4 == $complete_recharge_result['status_code']) {
				$msg['info'] = '已操作到款登记';
				$msg['status'] = 2;
				$msg['url'] = U('Admin/Analyse/cinemaFinancialReg') ;
			} else {
				$msg['info'] = '到款登记失败';
				$msg['status'] = 2;
				$msg['url'] = U('Admin/Analyse/cinemaFinancialReg') ;
			}
			
			$this->ajaxReturn($msg);

		}

	/*
     * web平台财务统计
     * @param  array
     * @return array
     * @author ming.nie
     */
    public function financial()
    {
		$model = M();
        $current_time = time();
        $today = date('Y-m-d', $current_time);
		
		$create_time_start = I('request.create_time_start');
		$create_time_end = I('request.create_time_end');
		
		$area_1 = I('request.mutil_level_data_1_id_1');
		$area_2 = I('request.mutil_level_data_1_id_2');
		$area_3 = I('request.mutil_level_data_1_id_3');
		
		if(!$area_1) {
			$area_1 = 0;
			$area_2 = 0;
			$area_3 = 0;
		}
		if(!$area_2) {
			$area_2 = 0;
			$area_3 = 0;
		}
		if(!$area_3) {
			$area_3 = 0;
		}
		
		if($create_time_start) {
			$time_start = strtotime($create_time_start.' 00:00:00');
		}
		if($create_time_end) {
			$time_end = strtotime($create_time_end.' 23:59:59');
		}
        
		//支付流水统计****************************************************
		//支付统计查询sql
		$pay_money_select_sql = ' AND a.refund = 0';
		if($create_time_start) {
			$pay_money_select_sql .= ' AND a.time >= '.$time_start;
		}
		if($time_end) {
			$pay_money_select_sql .= ' AND a.time <= '.$time_end;
		}
		if($area_1) {
			$pay_money_select_sql .= ' AND c.area_1 = '.$area_1;
		}
		if($area_2) {
			$pay_money_select_sql .= ' AND c.area_2 = '.$area_2;
		}
		if($area_3) {
			$pay_money_select_sql .= ' AND c.area_3 = '.$area_3;
		}
		
		//会员卡
		$sql = 'SELECT SUM(a.money) AS total_money 
				FROM mt_order_pay_method AS a 
				LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
				LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
				WHERE a.method = 4'.$pay_money_select_sql;
		$rs = $model->query($sql);
		$pay_money_4 = $rs[0]['total_money'];
		!$pay_money_4 && $pay_money_4 = 0;
		
		//券支付
		$sql = 'SELECT SUM(a.money) AS total_money 
				FROM mt_order_pay_method AS a 
				LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
				LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
				WHERE a.method = 5'.$pay_money_select_sql;
		$rs = $model->query($sql);
		$pay_money_5 = $rs[0]['total_money'];
		!$pay_money_5 && $pay_money_5 = 0;
		
		//支付宝
		$sql = 'SELECT SUM(a.money) AS total_money 
				FROM mt_order_pay_method AS a 
				LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
				LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
				WHERE a.method = 6'.$pay_money_select_sql;
		$rs = $model->query($sql);
		$pay_money_6 = $rs[0]['total_money'];
		!$pay_money_6 && $pay_money_6 = 0;
		
		//微信
		$sql = 'SELECT SUM(a.money) AS total_money 
				FROM mt_order_pay_method AS a 
				LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
				LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
				WHERE a.method = 7'.$pay_money_select_sql;
		$rs = $model->query($sql);
		$pay_money_7 = $rs[0]['total_money'];
		!$pay_money_7 && $pay_money_7 = 0;
		
		//支付流水总计
		$pay_money_total = $pay_money_4 + $pay_money_5 + $pay_money_6 + $pay_money_7;
		
		//退款流水统计****************************************************
		//退款统计查询sql
		$refund_money_select_sql = ' AND refund = 1';
		if($create_time_start) {
			$refund_money_select_sql .= ' AND a.time >= '.$time_start;
		}
		if($time_end) {
			$refund_money_select_sql .= ' AND a.time <= '.$time_end;
		}
		if($area_1) {
			$refund_money_select_sql .= ' AND c.area_1 = '.$area_1;
		}
		if($area_2) {
			$refund_money_select_sql .= ' AND c.area_2 = '.$area_2;
		}
		if($area_3) {
			$refund_money_select_sql .= ' AND c.area_3 = '.$area_3;
		}
		
		//会员卡
		$sql = 'SELECT SUM(a.money) AS total_money 
				FROM mt_order_pay_method AS a 
				LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
				LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
				WHERE a.method = 4'.$refund_money_select_sql;
		$rs = $model->query($sql);
		$refund_money_4 = $rs[0]['total_money'];
		!$refund_money_4 && $refund_money_4 = 0;
		
		//券支付
		$sql = 'SELECT SUM(a.money) AS total_money 
				FROM mt_order_pay_method AS a 
				LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
				LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
				WHERE a.method = 5'.$refund_money_select_sql;
		$rs = $model->query($sql);
		$refund_money_5 = $rs[0]['total_money'];
		!$refund_money_5 && $refund_money_5 = 0;
		
		//支付宝
		$sql = 'SELECT SUM(a.money) AS total_money 
				FROM mt_order_pay_method AS a 
				LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
				LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
				WHERE a.method = 6'.$refund_money_select_sql;
		$rs = $model->query($sql);
		$refund_money_6 = $rs[0]['total_money'];
		!$refund_money_6 && $refund_money_6 = 0;
		
		//微信
		$sql = 'SELECT SUM(a.money) AS total_money 
				FROM mt_order_pay_method AS a 
				LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
				LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
				WHERE a.method = 7'.$refund_money_select_sql;
		$rs = $model->query($sql);
		$refund_money_7 = $rs[0]['total_money'];
		!$refund_money_7 && $refund_money_7 = 0;
		
		//支付流水总计
		$refund_money_total = $refund_money_4 + $refund_money_5 + $refund_money_6 + $refund_money_7;
		
		//支付折线图****************************************************
		$pay_day_list = array();
		//确认统计时间段：没有开始时间，结束时间
		if(!$create_time_end) {
			$line_day_end = $today;
		} else {
			$line_day_end = $create_time_end;
		}
		$line_time_end = strtotime($line_day_end.' 00:00:00');
		if(!$create_time_start) {
			$line_day_time = strtotime($line_day_end.' 00:00:00') - 9 * 86400;
			$line_day_start = date('Y-m-d', $line_day_time);
		} else {
			$line_day_start = $create_time_start;
		}
		$line_time_start = strtotime($line_day_start.' 00:00:00');
		
		//折线图统计查询sql
		$line_select_sql = ' AND refund = 0';
		
		if($area_1) {
			$line_select_sql .= ' AND c.area_1 = '.$area_1;
		}
		if($area_2) {
			$line_select_sql .= ' AND c.area_2 = '.$area_2;
		}
		if($area_3) {
			$line_select_sql .= ' AND c.area_3 = '.$area_3;
		}
		
		for($i=$line_time_start; $i<=$line_time_end; $i=$i+86400) {
			$temp['day'] = date('m月d日', $i);
			$temp_max_time = $i+86400;
			$temp_line_select_sql = ' AND a.time >= '.$i.' AND a.time < '.$temp_max_time;
			
			$temp_sql = 'SELECT SUM(a.money) AS total_money 
						FROM mt_order_pay_method AS a 
						LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
						LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
						WHERE a.method = 4'.$temp_line_select_sql;
			$temp_rs = $model->query($temp_sql);
			$temp['pay_money_4'] = $temp_rs[0]['total_money'];
			!$temp['pay_money_4'] && $temp['pay_money_4'] = 0;
			
			$temp_sql = 'SELECT SUM(a.money) AS total_money 
						FROM mt_order_pay_method AS a 
						LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
						LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
						WHERE a.method = 5'.$temp_line_select_sql;
			$temp_rs = $model->query($temp_sql);
			$temp['pay_money_5'] = $temp_rs[0]['total_money'];
			!$temp['pay_money_5'] && $temp['pay_money_5'] = 0;
			
			$temp_sql = 'SELECT SUM(a.money) AS total_money 
						FROM mt_order_pay_method AS a 
						LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
						LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
						WHERE a.method = 6'.$temp_line_select_sql;
			$temp_rs = $model->query($temp_sql);
			$temp['pay_money_6'] = $temp_rs[0]['total_money'];
			!$temp['pay_money_6'] && $temp['pay_money_6'] = 0;
			
			$temp_sql = 'SELECT SUM(a.money) AS total_money 
						FROM mt_order_pay_method AS a 
						LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
						LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
						WHERE a.method = 7'.$temp_line_select_sql;
			$temp_rs = $model->query($temp_sql);
			$temp['pay_money_7'] = $temp_rs[0]['total_money'];
			!$temp['pay_money_7'] && $temp['pay_money_7'] = 0;
			
			$pay_day_list[] = $temp;
			
		}
		
		//影院收入统计柱状图****************************************************
		//查询签约影院
		$area_select_sql = '';
		if($area_1) {
			$area_select_sql .= ' AND area_1 = '.$area_1;
		}
		if($area_2) {
			$area_select_sql .= ' AND area_2 = '.$area_2;
		}
		if($area_3) {
			$area_select_sql .= ' AND area_3 = '.$area_3;
		}
		$sql = 'SELECT host_name,cinema_name FROM mt_cinema_host WHERE type=1 AND is_online=1'.$area_select_sql;
		$cinema_host = $model->query($sql);
		
		//影院收入统计柱查询sql
		$income_select_sql = ' AND a.refund = 0';
		if($create_time_start) {
			$income_select_sql .= ' AND a.time >= '.$time_start;
		}
		if($time_end) {
			$income_select_sql .= ' AND a.time <= '.$time_end;
		}
		
		$cinema_host_list = array();
		foreach($cinema_host as $key => $value) {
			$temp = $value;
			$temp_sql = 'SELECT SUM(a.money) AS total_money 
						FROM mt_order_pay_method AS a 
						LEFT JOIN mt_order AS b ON a.net_order_sn = b.net_order_sn 
						LEFT JOIN mt_cinema_host AS c ON b.host_name = c.host_name 
						WHERE b.host_name = "'.$value['host_name'].'"'.$income_select_sql;
			$temp_rs = $model->query($temp_sql);
			$temp['income'] = $temp_rs[0]['total_money'];
			!$temp['income'] && $temp['income'] = 0;
			
			$cinema_host_list[] = $temp;
		}
		
		foreach ($cinema_host_list as $key => $row) {
            $volume[$key] = $row['income'];
        }
        array_multisort($volume, SORT_DESC, $cinema_host_list);
		
        $page_title = '财务统计';
        $this->assign(get_defined_vars());
        $this->display('financial');
    }
}